{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#下载微信文章到数据库，批量版\n",
    "#v2.0.1 2018.12.3-16.01 添加新文章"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "=====1.end====\n"
     ]
    }
   ],
   "source": [
    "# 函数库\n",
    "import re, requests, sys, pymysql\n",
    "\n",
    "\n",
    "def getInfoByURL(url):\n",
    "    #####################################\n",
    "    #1.爬虫获取信息\n",
    "    #####################################\n",
    "    # 发出请求\n",
    "    respose=requests.get(msg_url)\n",
    "    assert respose.status_code==200,'Error: requests.get'\n",
    "    #print(respose.text)\n",
    "\n",
    "    #1.msg_title\n",
    "    title=re.findall(r'msg_title = \"(.*?)\"',respose.text,re.S)[0]\n",
    "    #print('title=',title)\n",
    "\n",
    "    #2.msg_desc\n",
    "    msg_desc=re.findall(r'msg_desc = \"(.*?)\"',respose.text,re.S)[0]\n",
    "    #print('msg_desc=',msg_desc)\n",
    "\n",
    "    #3.js_content\n",
    "    rs=re.findall(r'id=\"js_content\"\\>(.*?)\\<\\/div\\>',respose.text,re.S)[0]\n",
    "    #print('rs=', rs)\n",
    "\n",
    "\n",
    "    #4.获取日期\n",
    "    #aid1 由第一行获取数组：日期字符串，单词数\n",
    "    def getDateStrSize(strs):\n",
    "        d1=strs   #rs[1:300];d1\n",
    "        d2=re.split(\"words\",d1)[0]\n",
    "        d3=re.split(\">\",d2)[-1]\n",
    "        d4=re.split(\"\\|\", d3)\n",
    "\n",
    "        return [d4[0].strip(), int(d4[1].strip())] ##'Aug 30, 2018'日期; 859 单词数\n",
    "    d_n=getDateStrSize(rs[1:300]);\n",
    "    #print(d_n)  ##['Aug 30, 2018', 859]\n",
    "\n",
    "    #aid2 由日期字符串'Aug 30, 2018'获得日期数字字符串 '20180830'\n",
    "    def getDateFromStr(s):\n",
    "        a = s; #d4[0].strip()\n",
    "        month=a[0:3]; month\n",
    "        year=a[-4:];year\n",
    "        day=re.split(\"[\\s,]\",a)[1];day\n",
    "        #\n",
    "        m_dict={\"Jan\":\"01\", \"Feb\":\"02\", \"Mar\":\"03\", \"Apr\":\"04\", \"May\":\"05\", \"Jun\":\"06\", \n",
    "                \"Jul\":\"07\", \"Aug\":\"08\", \"Sep\":\"09\", \"Oct\":\"10\", \"Nov\":\"11\", \"Dec\":\"12\" }\n",
    "        #\n",
    "        def to2(day):\n",
    "            if len(day)==1:\n",
    "                day=\"0\"+day\n",
    "            return day;\n",
    "\n",
    "        return year+m_dict[month]+to2(day)\n",
    "\n",
    "    dateN=getDateFromStr(d_n[0]) #'20180830'\n",
    "\n",
    "    #5.获取字数\n",
    "    size=d_n[1]\n",
    "    #print(dateN, size)\n",
    "    \n",
    "    \n",
    "    #写入文件\n",
    "    with open('/home/wangjl/web/en'+dateN+'.html','w') as f:\n",
    "        f.write('<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\">')\n",
    "        f.write(title+\"<br>\")\n",
    "        f.write(msg_desc)\n",
    "        f.write(rs)\n",
    "    print(\"Title: \", title)\n",
    "    print(\"write in to file: http://y.biomooc.com/wangjl/en\"+dateN+\".html\")\n",
    "    \n",
    "    return [url,dateN, size,title,msg_desc,rs]\n",
    "\n",
    "\n",
    "    \n",
    "#####################################\n",
    "#2.写入数据库\n",
    "#####################################\n",
    "\n",
    "#db:wang\n",
    "#CREATE TABLE `msg_English` (\n",
    "#\t`msg_id` int(4) not null primary key auto_increment,\n",
    "#  `msg_url` varchar(300) , #url\n",
    "#\t\n",
    "#  `msg_origin` char(100) NOT NULL DEFAULT '', #来源\n",
    "#  `msg_date` char(50) NOT NULL DEFAULT '', #日期\n",
    "#  `msg_size` int(10) NOT NULL DEFAULT 0, #字数\n",
    "#\n",
    "#  `msg_title` char(100) NOT NULL DEFAULT '', \n",
    "#  `msg_desc` char(150) NOT NULL DEFAULT '',\n",
    "#  `msg_content` text #PRIMARY KEY (`msg_origin`,`msg_date`)\n",
    "#) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='微信英文文章表';\n",
    "\n",
    "def insertInfo2DB(info,source=\"英语读报\"):\n",
    "    # 打开数据库连接\n",
    "    db = pymysql.connect(host='y.biomooc.com',port=7070,user='root', password='123456', database='wang')\n",
    "\n",
    "    # 使用cursor()方法获取操作游标 \n",
    "    cursor = db.cursor()\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "    #如果没有，则保存到数据库中\n",
    "    def DoInsertDB(info,source):\n",
    "        # SQL 插入语句\n",
    "        sql = \"INSERT INTO msg_English(msg_url,msg_origin,msg_date, msg_size, msg_title, msg_desc,msg_content) \\\n",
    "               VALUES ('%s', '%s', '%s', '%d', '%s', '%s','%s' )\" % \\\n",
    "              (info[0],source, info[1],info[2],info[3],info[4],pymysql.escape_string(info[5]))\n",
    "                #[url,dateN, size,title,msg_desc,rs]\n",
    "               #(msg_url, '英语读报', dateN, size, title, msg_desc, pymysql.escape_string(rs))\n",
    "        try:\n",
    "            cursor.execute(sql) # 执行sql语句\n",
    "            db.commit() # 提交到数据库执行\n",
    "            print(\"commit successfully\")\n",
    "        except:\n",
    "            rsponse=db.rollback() # 发生错误时回滚\n",
    "            print(\"rollback\")\n",
    "            print( sys.exc_info() )\n",
    "\n",
    "    #如果日期已经存在，则不进行处理\n",
    "\n",
    "\n",
    "    # SQL 查询语句\n",
    "    dateN2=info[1]\n",
    "    #dateN2=\"20170102\"\n",
    "    sql = 'select msg_date from msg_English where msg_date like \"'+dateN2+'\";'\n",
    "\n",
    "    # 执行sql语句\n",
    "    cursor.execute(sql)\n",
    "    values = cursor.fetchall()\n",
    "    if len(values)==0:\n",
    "        print(\"Not exist the date\")\n",
    "        DoInsertDB(info,source)\n",
    "    else:\n",
    "        print(\"The date \"+dateN2+\" has already existed.\")\n",
    "\n",
    "    # 关闭数据库连接\n",
    "    db.close()\n",
    "\n",
    "print(\"=====1.end====\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10\n",
      "https://mp.weixin.qq.com/s?timestamp=1543823695&src=3&ver=1&signature=pGFsXhjV7IozHY5PyM1RMbbtB14HwUxvHw3drWTnrG7TZFZle6E1jLkJxWsaGOJ4rmIWW1iQRGQz6eoUJjckpy145UgxG0H9aJU3fbqlPIMFT7RXGlOYIJs4HTTY0vH9PsJIrZK1UflXmSM96l9NKsCXOtzUtxDC4UUGzcoJLZk=\n",
      "==================== 预期会有10个url，请手工测试一个url能否打开。\n"
     ]
    }
   ],
   "source": [
    "#预备 测试url 获取\n",
    "\n",
    "#如何批量获取微信文章的url?\n",
    "import requests\n",
    "from lxml import html\n",
    "\n",
    "#1.请求数据\n",
    "\n",
    "###############\n",
    "#1.手工访问该页面\n",
    "# https://weixin.sogou.com/weixin?type=2&s_from=input&query=英语读报&ie=utf8&_sug_=n&_sug_type_= \n",
    "# 右侧点击公众号\n",
    "#2. 然后F12，把html全部从控制台复制出来。\n",
    "#3. cat命令保存到/home/wangjl/web/urls.html文件中。 \n",
    "###############\n",
    "\n",
    "urls=\"http://y.biomooc.com/wangjl/urls.html\"\n",
    "page=requests.Session().get(urls)\n",
    "#print(\"==ok  http://y.biomooc.com/wangjl/urls.html\")\n",
    "\n",
    "\n",
    "#2.解析数据：获取批量URL\n",
    "tree=html.fromstring(page.text)\n",
    "\n",
    "result=tree.xpath('//@hrefs')#重复出现2次，所以只取奇数即可。\n",
    "arrURL=[]\n",
    "for i in range( len(result) ):\n",
    "    if i%2==0:\n",
    "        arrURL.append(\"https://mp.weixin.qq.com\"+result[i])\n",
    "print(len(arrURL))\n",
    "print(arrURL[0])\n",
    "\n",
    "print(\"=\"*20, \"预期会有10个url，请手工测试一个url能否打开。\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://mp.weixin.qq.com/s?timestamp=1543823695&src=3&ver=1&signature=pGFsXhjV7IozHY5PyM1RMbbtB14HwUxvHw3drWTnrG7TZFZle6E1jLkJxWsaGOJ4rmIWW1iQRGQz6eoUJjckpy145UgxG0H9aJU3fbqlPIMFT7RXGlOYIJs4HTTY0vH9PsJIrZK1UflXmSM96l9NKsCXOtzUtxDC4UUGzcoJLZk=\n",
      "Title:  economist|Chip wars: China, America and silicon supremacy\n",
      "write in to file: http://y.biomooc.com/wangjl/en20181203.html\n",
      "Not exist the date\n",
      "commit successfully\n",
      "0 6.819681301416124\n",
      "https://mp.weixin.qq.com/s?timestamp=1543823695&src=3&ver=1&signature=pGFsXhjV7IozHY5PyM1RMbbtB14HwUxvHw3drWTnrG7TZFZle6E1jLkJxWsaGOJ4rmIWW1iQRGQz6eoUJjckp1zsDlNmIuiEeBpapVZIVPhM9yMEfV9QiRRe9mAtSmrEiU8bUPXV-XdEzQPa3IgHUWLBAkG0TOtofUgOuX7rlSY=\n",
      "Title:  Time|The True Story Behind the Vincent van Gogh Biopic At ...\n",
      "write in to file: http://y.biomooc.com/wangjl/en20181202.html\n",
      "Not exist the date\n",
      "commit successfully\n",
      "1 2.711133284607418\n",
      "https://mp.weixin.qq.com/s?timestamp=1543823695&src=3&ver=1&signature=pGFsXhjV7IozHY5PyM1RMbbtB14HwUxvHw3drWTnrG7TZFZle6E1jLkJxWsaGOJ4rmIWW1iQRGQz6eoUJjckp*0N7MSOtwQtnJUnyNuSfzEyiNIWKDAP9571W-8rgmOfvD6qBYpi831DahcagtGXRzmQqr3dXh9IchbPtOtAiUM=\n",
      "Title:  economist | A history of China in 8m objects\n",
      "write in to file: http://y.biomooc.com/wangjl/en20181201.html\n",
      "Not exist the date\n",
      "commit successfully\n",
      "2 4.2171808724241435\n",
      "https://mp.weixin.qq.com/s?timestamp=1543823695&src=3&ver=1&signature=pGFsXhjV7IozHY5PyM1RMbbtB14HwUxvHw3drWTnrG7TZFZle6E1jLkJxWsaGOJ4rmIWW1iQRGQz6eoUJjckpzgkBirfueMbWXyoi1jNMmkpBCCMH-lQLJ5wSixetUGNIh9vjghANPS3npI2gSgGUGHav-omwvpRju-eai5N9Ew=\n",
      "Title:  Time|These Are the Best Black Friday Deals in Tech\n",
      "write in to file: http://y.biomooc.com/wangjl/en20181130.html\n",
      "Not exist the date\n",
      "commit successfully\n",
      "3 9.875309954631904\n",
      "https://mp.weixin.qq.com/s?timestamp=1543823695&src=3&ver=1&signature=pGFsXhjV7IozHY5PyM1RMbbtB14HwUxvHw3drWTnrG7TZFZle6E1jLkJxWsaGOJ4rmIWW1iQRGQz6eoUJjckp6gWsVbEzVxjyBts3WmLYO3M7I8olA4oTgQ5VWBc-qda6*JkTQVaK0MyfQNkCeiTsONdeK0udiCE*PlXy3s8bZw=\n",
      "Title:  nature|How the genome-edited babies revelation will affect ...\n",
      "write in to file: http://y.biomooc.com/wangjl/en20181129.html\n",
      "The date 20181129 has already existed.\n",
      "4 6.316837524806992\n",
      "https://mp.weixin.qq.com/s?timestamp=1543823695&src=3&ver=1&signature=pGFsXhjV7IozHY5PyM1RMbbtB14HwUxvHw3drWTnrG7TZFZle6E1jLkJxWsaGOJ4rmIWW1iQRGQz6eoUJjckpyv0yjvTyRztaiNu488MPadwDr3TcpAQ7xgLKo8rKbJQMhhs8vPYE78uZJonDpuxI7JpCJ6h2QxKEqiIRp9p1QQ=\n",
      "Title:  Time|Here&#39;s Why the CDC Wants You to Throw Away All Your ...\n",
      "write in to file: http://y.biomooc.com/wangjl/en20181128.html\n",
      "The date 20181128 has already existed.\n",
      "5 3.409990300443573\n",
      "https://mp.weixin.qq.com/s?timestamp=1543823695&src=3&ver=1&signature=pGFsXhjV7IozHY5PyM1RMbbtB14HwUxvHw3drWTnrG7TZFZle6E1jLkJxWsaGOJ4rmIWW1iQRGQz6eoUJjckp-2-YAEHiI0ekjYLSO3COrRFDCW2WFYiulN8pqQjJ4KJFvL81JIN2mteMyfsS1FRMK7*da3PcqcwioIZNHfE14A=\n",
      "Title:  BB|China Scientist’s Claim on World’s First Gene-Edited Babies\n",
      "write in to file: http://y.biomooc.com/wangjl/en20181127.html\n",
      "The date 20181127 has already existed.\n",
      "6 7.305138537911592\n",
      "https://mp.weixin.qq.com/s?timestamp=1543823695&src=3&ver=1&signature=pGFsXhjV7IozHY5PyM1RMbbtB14HwUxvHw3drWTnrG7TZFZle6E1jLkJxWsaGOJ4rmIWW1iQRGQz6eoUJjckp-CGrGBjWNDLcsu4epHQJTQcIHpLr1r7GWhBowxf0pkuwA7f8H2dZLwJ0bhbClwCMqP2*2WcJ7u8eF-G*J-6T-I=\n",
      "Title:  nature|Quantum computers put blockchain security at risk\n",
      "write in to file: http://y.biomooc.com/wangjl/en20181126.html\n",
      "The date 20181126 has already existed.\n",
      "7 4.148775992513309\n",
      "https://mp.weixin.qq.com/s?timestamp=1543823695&src=3&ver=1&signature=pGFsXhjV7IozHY5PyM1RMbbtB14HwUxvHw3drWTnrG7TZFZle6E1jLkJxWsaGOJ4rmIWW1iQRGQz6eoUJjckpx5fjR1IM6YbEgmPLQtiuBM6tT*OVdungsUAcVjm2pyW7*Y5uxhgdLP9MBGTIZvwXH08Ej178O73YFBsXWLp3Uc=\n",
      "Title:  Time|Why America’s Founders Tried to Recruit a Foreign Prince...\n",
      "write in to file: http://y.biomooc.com/wangjl/en20181125.html\n",
      "The date 20181125 has already existed.\n",
      "8 7.564966404209674\n",
      "https://mp.weixin.qq.com/s?timestamp=1543823695&src=3&ver=1&signature=pGFsXhjV7IozHY5PyM1RMbbtB14HwUxvHw3drWTnrG7TZFZle6E1jLkJxWsaGOJ4rmIWW1iQRGQz6eoUJjckpwSyNEh3TjjBXNSIM6POvX4zaqMx4NUyt1-OShS8T4rf-RuTtgoXx268BwV-9uPIbfBD6qJQ1OFW1OojhJxmO7o=\n",
      "Title:  economist | A scientist makes the case for the power of DNA\n",
      "write in to file: http://y.biomooc.com/wangjl/en20181124.html\n",
      "The date 20181124 has already existed.\n",
      "9 10.650643638534612\n",
      "\n",
      "=======================2.end\n"
     ]
    }
   ],
   "source": [
    "#批量实施：下载并保存到文件和数据库\n",
    "import requests\n",
    "from lxml import html\n",
    "\n",
    "#1.请求数据\n",
    "\n",
    "#\n",
    "#\n",
    "###############\n",
    "#1.手工访问该页面\n",
    "# https://weixin.sogou.com/weixin?type=2&s_from=input&query=英语读报&ie=utf8&_sug_=n&_sug_type_= 右侧点击公众号\n",
    "#2. 然后F12，把html全部从控制台复制出来。\n",
    "#3. cat命令保存到/home/wangjl/web/urls.html文件中。 \n",
    "###############\n",
    "urls=\"http://y.biomooc.com/wangjl/urls.html\"\n",
    "page=requests.Session().get(urls)\n",
    "#print(\"==ok  http://y.biomooc.com/wangjl/urls.html\")\n",
    "\n",
    "\n",
    "#2.解析数据：获取批量URL\n",
    "tree=html.fromstring(page.text)\n",
    "#result=tree.xpath('//ul/li[contains(@class,\"ui-slide-item\")]/attribute::data-title')#hit 32 items \n",
    "\n",
    "result=tree.xpath('//@hrefs')#重复出现2次，所以只取奇数即可。\n",
    "arrURL=[]\n",
    "for i in range( len(result) ):\n",
    "    if i%2==0:\n",
    "        arrURL.append(\"https://mp.weixin.qq.com\"+result[i])\n",
    "#print(len(arrURL))\n",
    "#arrURL[0]\n",
    "\n",
    "\n",
    "\n",
    "#3. 批量URL获取文章，并插入数据库\n",
    "import time,random\n",
    "for i in range(len(arrURL)): #随机时间段后[2s,12s]执行\n",
    "#for i in range(3):    \n",
    "    msg_url=arrURL[i]\n",
    "    print(msg_url)\n",
    "    #2.get Info  return [url,dateN, size,title,msg_desc,rs]\n",
    "    info= getInfoByURL(msg_url)\n",
    "\n",
    "    #3.插入数据库\n",
    "    insertInfo2DB(info)\n",
    "    \n",
    "    #等待随机时间\n",
    "    pause=2+10*random.random()\n",
    "    print(str(i)+' '+str(pause))\n",
    "    time.sleep(pause)\n",
    "\n",
    "print(\"\\n=======================2.end\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://mp.weixin.qq.com/s?timestamp=1542804854&src=3&ver=1&signature=9pNdKe*6eoeW2LOZb*bLOQhkhDUBekOk6xuXdQ0221KhpCOKq6KfLSLsqmIs9mxpGD2LptVSykNib2pDxSyZCRAawV0nm6atfH6NvWa8NKyOsAYp1YmA2wrC2o9qArBi8hsQtoEzJMH9korF7tD9pVNAXjK*IlRKV9rvGFpe7IQ=\n",
      "write in to file: http://y.biomooc.com/wangjl/en20181112.html\n",
      "Not exist the date\n",
      "commit successfully\n"
     ]
    }
   ],
   "source": [
    "# 单个URL的下载和插入数据库\n",
    "#实施步骤 \n",
    "\n",
    "#1.set URL\n",
    "#msg_url='https://mp.weixin.qq.com/s?__biz=MzI0NDcxNzc5Mg==&mid=2247485084&idx=1&sn=70239065aaa1f59610dab9894df7f745'\n",
    "msg_url=arrURL[9]\n",
    "print(msg_url)\n",
    "\n",
    "#2.get Info  return [url,dateN, size,title,msg_desc,rs]\n",
    "info= getInfoByURL(msg_url)\n",
    "\n",
    "#3.插入数据库\n",
    "insertInfo2DB(info)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['英语读报', '20180830', 'Time|A New Study Says Any Amount of Drinking Is Bad for You', 859]\n",
      "['英语读报', '20181112', 'cnbc | Mark says it’s time to buy stocks in emerging markets', 570]\n",
      "['英语读报', '20181113', 'HWR|Stan Lee, Marvel Comics&#39; Real-Life Superhero, Dies at 95', 1501]\n",
      "['英语读报', '20181114', 'nature|China’s crackdown on genetics breaches could deter data..', 945]\n",
      "['英语读报', '20181115', 'newRepublic | Stan Lee: the Midwife of the Marvel Universe', 1065]\n",
      "['英语读报', '20181116', 'economist | Automation in Japan: Human endurance', 527]\n",
      "['英语读报', '20181117', 'cnn | Which country has the best food?', 451]\n",
      "['英语读报', '20181118', 'Fox | Google replaces CEO of cloud unit with former Oracle exec', 231]\n",
      "['英语读报', '20181119', 'economist | China’s internet titan has had a bruising 2018', 1076]\n",
      "['英语读报', '20181120', 'science|Large, strangely dim galaxy found lurking on far side...', 583]\n",
      "['英语读报', '20181121', 'economist | Universities’ finances: After the boom, the bust', 803]\n",
      "['英语读报', '20181122', 'guardian|Steve Jobs honoured by Silicon Valley', 261]\n",
      "['英语读报', '20181123', 'Time|D&amp;G Faces Backlash in China After an Ad Prompted ...', 586]\n",
      "['英语读报', '20181124', 'economist | A scientist makes the case for the power of DNA', 732]\n",
      "['英语读报', '20181125', 'Time|Why America’s Founders Tried to Recruit a Foreign Prince...', 968]\n",
      "['英语读报', '20181126', 'nature|Quantum computers put blockchain security at risk', 1277]\n",
      "['英语读报', '20181127', 'BB|China Scientist’s Claim on World’s First Gene-Edited Babies', 815]\n",
      "['英语读报', '20181128', 'Time|Here&#39;s Why the CDC Wants You to Throw Away All Your ...', 453]\n",
      "['英语读报', '20181129', 'nature|How the genome-edited babies revelation will affect ...', 869]\n",
      "['英语读报', '20181130', 'Time|These Are the Best Black Friday Deals in Tech', 910]\n",
      "['英语读报', '20181201', 'economist | A history of China in 8m objects', 831]\n",
      "['英语读报', '20181202', 'Time|The True Story Behind the Vincent van Gogh Biopic At ...', 473]\n",
      "['英语读报', '20181203', 'economist|Chip wars: China, America and silicon supremacy', 878]\n"
     ]
    }
   ],
   "source": [
    "# 获取mysql已有文章的目录\n",
    "\n",
    "#!/usr/bin/python\n",
    "# -*- coding: UTF-8 -*-\n",
    "import pymysql\n",
    "\n",
    "#mysql 获取数据\n",
    "\n",
    "# 打开数据库连接\n",
    "db = pymysql.connect(host='y.biomooc.com',port=7070,user='root', password='123456', database='wang')\n",
    "\n",
    "# 使用cursor()方法获取操作游标 \n",
    "cursor = db.cursor()\n",
    "\n",
    "# SQL 插入语句\n",
    "#sql = \"select msg_date,msg_title,msg_size from msg_English order by msg_size DESC\" #按照字数倒序\n",
    "sql = \"select msg_origin,msg_date,msg_title,msg_size from msg_English order by msg_date\"\n",
    "\n",
    "# 执行sql语句\n",
    "cursor.execute(sql)\n",
    "values = cursor.fetchall()\n",
    "\n",
    "for i in range(len(values)):\n",
    "    print( list(values[i]) )\n",
    "\n",
    "# 关闭数据库连接\n",
    "db.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "hit 2 item(s):  overall\n",
      "======================================== \n",
      " 20180830 Time|A New Study Says Any Amount of Drinking Is Bad for You < 859 > words\n",
      "1| A new study concludes there’s no amount of alcohol consumption that’s safe for overall health — a finding that’s likely to surprise moderate drinkers, and that has left some experts unconvinced\n",
      "\n",
      "2| “We don’t have the information for specific individuals…we’re making overall recommendations at the population level,” she says\n",
      "\n",
      "3|  “If you’re running a health system in a country, it’s better overall for the population of your country to not drink at all than to drink a little bit\n",
      "\n",
      "4| “I think this is actually consistent with every organization’s recommendation that, overall, no one should start drinking to prevent heart disease or diabetes,” Mozaffarian says\n",
      "\n",
      "======================================== \n",
      " 20181114 nature|China’s crackdown on genetics breaches could deter data.. < 945 > words\n",
      "5|  Although the penalties set back certain research projects, the companies say that overall they have not had a large impact on their research\n",
      "\n",
      "==end==\n"
     ]
    }
   ],
   "source": [
    "# 根据关键词，搜索mysql中匹配的例句\n",
    "\n",
    "#!/usr/bin/python\n",
    "# -*- coding: UTF-8 -*-\n",
    "import pymysql,re\n",
    "\n",
    "# keyword\n",
    "#keyword=\"caveat\"\n",
    "#keyword=\"moderate\"\n",
    "keyword=\"overall\"\n",
    "\n",
    "#mysql 获取数据\n",
    "\n",
    "# 打开数据库连接\n",
    "db = pymysql.connect(host='y.biomooc.com',port=7070,user='root', password='123456', database='wang')\n",
    "\n",
    "# 使用cursor()方法获取操作游标 \n",
    "cursor = db.cursor()\n",
    "\n",
    "# SQL 插入语句\n",
    "#sql = \"select msg_date,msg_title,msg_size from msg_English order by msg_size DESC\" #按照字数倒序\n",
    "sql = \"select msg_origin,msg_date,msg_title,msg_size,msg_content from msg_English where msg_content like '%\" +keyword+ \"%' order by msg_date\"\n",
    "\n",
    "# 执行sql语句\n",
    "cursor.execute(sql)\n",
    "values = cursor.fetchall()\n",
    "\n",
    "print('hit', len(values), 'item(s): ', keyword )\n",
    "\n",
    "counter=0\n",
    "for i in range(len(values)):\n",
    "    date=values[i][1]\n",
    "    title=values[i][2]\n",
    "    size=values[i][3]\n",
    "    text=re.sub(r'<[^>]+>', \"\", values[i][4])\n",
    "    \n",
    "    print('='*40,'\\n',date, title,\"<\",size,'> words')\n",
    "    \n",
    "    arr=re.split(\"[.?\\n]\",text) #todo 怎么保留句号呢？\n",
    "    for j in range( len(arr) ):\n",
    "        if len( re.findall(keyword,arr[j]) )>0:\n",
    "            counter+=1\n",
    "            print(counter,\"| \",arr[j],\"\\n\",sep=\"\")\n",
    "\n",
    "\n",
    "# 关闭数据库连接\n",
    "db.close()\n",
    "\n",
    "print(\"==end==\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
